﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class BookingFlightDAL
    {
        private const string SQL_SELECT_WS_BOOKING_FLIGHT = "select BKF_ID as PID, BKF_BOOKING_ID as BOOKING_ID,BKF_AIRLINE_ID as AIRLINE_ID,BKF_FLIGHT_NO as FLIGHT_NO,date_format(BKF_DEPARTURE_TIME,'%Y-%m-%d %H:%i:%s') as DEPARTURE_TIME,date_format(BKF_ARRIVAL_TIME,'%Y-%m-%d %H:%i:%s') as ARRIVAL_TIME,BKF_DEPARTURE_CITY_ID as DEPARTURE_CITY_ID,BKF_ARRIVAL_CITY_ID as ARRIVAL_CITY_ID,BKF_RCRE_STAFF_ID as RCRE_STAFF_ID,BKF_RCRE_TIME as RCRE_TIME,BKF_LCHG_STAFF_ID as LCHG_STAFF_ID,BKF_LCHG_TIME as LCHG_TIME,(select ALI_AIRLINE_NAME from WS_AIRLINE where ALI_AIRLINE_ID=BKF_AIRLINE_ID) as AIRLINE_NAME,(select concat(CIT_CITY_NAME,'(',CIT_AIRPORT_NAME,')') from WS_CITY where CIT_CITY_ID=BKF_DEPARTURE_CITY_ID) as DEPARTURE_CITY_NAME,(select concat(CIT_CITY_NAME,'(',CIT_AIRPORT_NAME,')') from WS_CITY where CIT_CITY_ID=BKF_ARRIVAL_CITY_ID) as ARRIVAL_CITY_NAME from WS_BOOKING_FLIGHT where 1=1 ";
        private const string SQL_INSERT_WS_BOOKING_FLIGHT = "insert into WS_BOOKING_FLIGHT(BKF_ID,BKF_BOOKING_ID,BKF_AIRLINE_ID,BKF_FLIGHT_NO,BKF_DEPARTURE_TIME,BKF_ARRIVAL_TIME,BKF_DEPARTURE_CITY_ID,BKF_ARRIVAL_CITY_ID,BKF_RCRE_STAFF_ID,BKF_RCRE_TIME) values(@PId,@BookingId,@AirlineId,@FlightNo,@DepartureTime,@ArrivalTime,@DepartureCityId,@ArrivalCityId,@LastStaffId,@LastTime)";
        private const string SQL_DELETE_WS_BOOKING_FLIGHT = "delete from WS_BOOKING_FLIGHT where 1=1 ";
        private const string SQL_COUNT_WS_BOOKING_FLIGHT = "select count(BKF_BOOKING_ID) from WS_BOOKING_FLIGHT where 1=1 ";

        private const string SQL_WHERE_ID = " and BKF_ID=@PId ";
        private const string SQL_WHERE_BOOKING_ID = " and BKF_BOOKING_ID=@BookingId ";
        private const string SQL_WHERE_AIRLINE_ID = " and BKF_AIRLINE_ID=@AirlineId ";
        private const string SQL_WHERE_DEPARTURE_CITY_ID = " and BKF_DEPARTURE_CITY_ID=@DepartureCityId ";
        private const string SQL_WHERE_ARRIVAL_CITY_ID = " and BKF_ARRIVAL_CITY_ID=@ArrivalCityId ";

        private const string PARM_ID = "@PId";
        private const string PARM_BOOKING_ID = "@BookingId";
        private const string PARM_AIRLINE_ID = "@AirlineId";
        private const string PARM_FLIGHT_NO = "@FlightNo";
        private const string PARM_DEPARTURE_TIME = "@DepartureTime";
        private const string PARM_ARRIVAL_TIME = "@ArrivalTime";
        private const string PARM_DEPARTURE_CITY_ID = "@DepartureCityId";
        private const string PARM_ARRIVAL_CITY_ID = "@ArrivalCityId";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";

        Nullable<DateTime> NullDateTime = null;

        private static BookingFlightDAL instance = new BookingFlightDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static BookingFlightDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet(string bookingId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_BOOKING_FLIGHT);
            sbSQL.Append(SQL_WHERE_BOOKING_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = bookingId });

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public List<BookingFlightModel> Gets(string bookingId)
        {
            using (DataSet ds = this.GetDataSet(bookingId))
            {
                DataTable dt = ds.Tables[0];
                List<BookingFlightModel> list = dt.AsEnumerable().Select(t => new BookingFlightModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    BookingId = Convert.ToString(t.ItemArray[1]),
                    AirlineId = Convert.ToString(t.ItemArray[2]),
                    FlightNo = Convert.ToString(t.ItemArray[3]),
                    DepartureTime = Convert.ToDateTime(t.ItemArray[4]),
                    ArrivalTime = Convert.ToDateTime(t.ItemArray[5]),
                    DepartureCityId = Convert.ToString(t.ItemArray[6]),
                    ArrivalCityId = Convert.ToString(t.ItemArray[7]),
                    CreatedStaffId = Convert.ToString(t.ItemArray[8]),
                    CreatedDate = Convert.ToDateTime(t.ItemArray[9]),
                    LastModifiedStaffId = t.IsNull(10) ? string.Empty : Convert.ToString(t.ItemArray[10]),
                    LastModifiedDate = t.IsNull(11) ? NullDateTime : Convert.ToDateTime(t.ItemArray[11])
                }).ToList<BookingFlightModel>();

                return list;
            }
        }
        public int Insert(DbTransaction trans, BookingFlightModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.BookingId });
            parms.Add(new MySqlParameter(PARM_AIRLINE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.AirlineId });
            parms.Add(new MySqlParameter(PARM_FLIGHT_NO, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.FlightNo });
            parms.Add(new MySqlParameter(PARM_DEPARTURE_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.DepartureTime });
            parms.Add(new MySqlParameter(PARM_ARRIVAL_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.ArrivalTime });
            parms.Add(new MySqlParameter(PARM_DEPARTURE_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.DepartureCityId });
            parms.Add(new MySqlParameter(PARM_ARRIVAL_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ArrivalCityId });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_BOOKING_FLIGHT, parms.ToArray());
        }
        public int Delete(DbTransaction trans, string bookingId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_BOOKING_FLIGHT);
            sbSQL.Append(SQL_WHERE_BOOKING_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = bookingId });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public bool AirlineUsing(string airlineId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_BOOKING_FLIGHT);
            sbSQL.Append(SQL_WHERE_AIRLINE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_AIRLINE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = airlineId });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool DepartureCityUsing(string departureCityId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_BOOKING_FLIGHT);
            sbSQL.Append(SQL_WHERE_DEPARTURE_CITY_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_DEPARTURE_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = departureCityId });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool ArrivalCityUsing(string arrivalCityId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_BOOKING_FLIGHT);
            sbSQL.Append(SQL_WHERE_ARRIVAL_CITY_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ARRIVAL_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = arrivalCityId });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
